package com.entrogy.dao;

import com.alibaba.fastjson.JSONObject;
import com.entrogy.bean.BannerEntity;
import com.entrogy.bean.SchoolEntity;
import com.entrogy.bean.SchoolThdidUrlEntity;
import com.entrogy.bean.StudentEntity;
import net.sf.json.JSONArray;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface SchoolMapper {
    @Insert("insert into school_information (school_code,school_name,user_id) values(#{schoolCode},#{schoolName},#{userId})")
	int insertSchool(SchoolEntity schoolEntity);

    @Select("select school_code from school_information where school_code like '%${schoolCode}%' order by school_code desc limit 1 ")
	Integer getSchoolCode(@Param("schoolCode") int schoolCode );

    @Select("select * from school_information where user_id = #{userId} and status = 00 order by update_time desc")
	List<SchoolEntity> getSchoolByUserId(@Param("userId") String userId);

    @Update("update school_information set school_name = #{schoolName} where school_code = #{schoolCode} and status = 00")
	int updateSchool(SchoolEntity schoolEntity);

    @Delete("update school_information set status = 01 where school_code = #{schoolCode}")
	int deleteSchool(@Param("schoolCode") int schoolCode);

    @Select("select * from student_information s where s.leave_type=2 and s.course_id in (select course_id from course_information where father_id = #{id}) ")
	List<StudentEntity> getPickStudent(@Param("id") int id);

    @Select("select * from parent_visitor_information where parent_visitor_id=(select parent1_id from student_information where id=#{id})")
	String getParentPhoneNum(int id);

    @Select("select * from school_information where status = 0")
	List<SchoolEntity> getTotalSchoolInformation();

    @Select("<script>"
            +"select s.school_code,s.user_id as userId,s.school_name,s.status,s.create_time as createTime,s.update_time as updateTime,u.name from school_information s"
            +" "
            +"LEFT JOIN user u ON u.id = s.user_id"
            +" "
            +"where 1=1"
            +" "
            + "<if test='schoolName!=null and schoolName != \"\" '>"
            + " and school_name LIKE \'%${schoolName}%\'"
            + "</if> "
            +" "
            +"and status = 00"
            + " "
            +"ORDER BY school_code "
            +" "
            +"<if test='page!=null and total != null '>"
            +"LIMIT #{page},#{total}"
            +"</if>"
            +"</script>")
    List<SchoolEntity> getSchoolList(Map<String,Object> map);

    @Select({"<script>"
            +"select count(school_code) from school_information "
            +"where 1=1 "
            + "<if test='schoolName!=null and schoolName != \"\"'> "
            + "and school_name LIKE \'%${schoolName}%\' "
            + "</if> "
            +" "
            +"and status = 00"
            + " "
            + "ORDER BY school_code "
            + "</script> "})
    Integer getSchoolListByTotal(Map<String,Object> map);

    @Insert("insert into school_banner(img_url,school_code) values(#{imgUrl},#{schoolCode})")
    int insertBanner(@Param("imgUrl") String imgUrl, @Param("schoolCode") int schoolCode);

    @Update("update school_banner set img_url = #{imgUrl} where id = #{id} and school_code = #{schoolCode}")
    int updateBanner(@Param("imgUrl") String imgUrl,@Param("id") Integer id, @Param("schoolCode") int schoolCode);

    @Select("select * from school_banner where school_code = #{schoolCode}")
    List<BannerEntity> getBanner(@Param("schoolCode") int schoolCode);

    @Delete("delete from school_banner where id = #{id}")
    int deleteBanner(@Param("id") int id);

    @Select("select count(*) from student_information where course_id=#{classId} and school_code=#{schoolCode}")
    int getClassStuNum(@Param(value = "classId") int firstClassId, @Param(value = "schoolCode") Integer schoolCode);

    /**
     * 查询一个学校的所有年级
     * @param schoolCode
     * @return
     */
    @Select("select id,class_name as name from class_information where school_code=#{schoolCode} and parent_id=1")
    JSONArray schoolAllGradeList(Integer schoolCode);

    @Select("<script>"
            + "select school_code as schoolCode,school_name as schoolName from school_information where 1=1"
            + "<if test='schoolCode != null'>"
            + " and school_code=#{schoolCode} "
            + "</if>"
            + "</script>")
    List<JSONObject> listSchool(@Param("schoolCode") Integer schoolCode);

    /**
     * 查询该学校对应实义德后台管理系统url以及人员组id
     * @param schoolCode
     * @return
     */
    @Select("select stc.id,ts.path_url,stc.school_code,stc.student_group_id,stc.parent_group_id,ts.id as thdidSystemId," +
            "stc.teacher_group_id,stc.visitor_group_id,ts.access_token,ts.device_model " +
            "from school_thdid_url stc inner join thdid_system ts " +
            "on stc.thdid_system_id=ts.id where stc.school_code=#{schoolCode} and ts.device_model=#{deviceModel} limit 1")
    SchoolThdidUrlEntity getSchoolThdidUrlAndGroupInfo(@Param("schoolCode") Integer schoolCode, @Param("deviceModel") String deviceModel);

    /**
     * 查询该学校对应型号的实义德后台管理系统url以及人员组id
     * @param schoolCode
     * @return
     */
    @Select("select ts.path_url from school_thdid_url stu inner join thdid_system ts on stu.thdid_system_id=ts.id where stu.school_code=#{schoolCode} and ts.device_model=#{deviceModel} limit 1")
    String getSchoolThdidUrl(@Param("schoolCode") Integer schoolCode, @Param("deviceModel") String deviceModel);

    /**
     * 查询系统所有学校的schoolCode列表
     * @return
     */
    @Select("select school_code from school_information")
    List<Integer> listSchoolCode();

    /**
     * 根据schoolCode查询学校名
     * @param schoolCode
     * @return
     */
    @Select("select school_name from school_information where school_code=#{schoolCode}")
    String getSchoolName(Integer schoolCode);

    /**
     * 查询学校试运行状态
     * @param schoolCode
     * @return
     */
    @Select("select open_status from school_information where school_code=#{schoolCode}")
    Integer getSchoolOpenStatus(Integer schoolCode);
}
